from nmj.tools.ordereddict import OrderedDict
import logging
import os
import sqlite3
_LOGGER = logging.getLogger(__name__)

class BaseNmjDB(object):
	isolation_level = "DEFERRED"
	def __init__(self, library_path):
		self.library_path = library_path
		if not self.library_path.endswith("/"):
			self.library_path += "/"
		self.media_db_directory = os.path.join(self.library_path, "nmj_database")
		self.media_db_path = os.path.join(self.media_db_directory, "media.db")
		self.image_directory = os.path.join(self.library_path, "nmj_database", "media", "video")
		if not os.path.isfile(self.media_db_path):
			self.create_db()
		_LOGGER.debug("create connection")
		self.connection = sqlite3.connect(self.media_db_path)
		self.connection.isolation_level = self.isolation_level
		self.connection.text_factory = str # sqlite3.OptimizedUnicode
		self.cursor = self.connection.cursor()

	def search_and_delete(self, table, **kwargs):
		items = table.load(self.cursor, **kwargs)
		for item in items:
			item.delete(self.cursor)


	def select(self, request, *args):
		return self.cursor.execute(request, args).fetchall()

	def get_all_table_names(self, cursor):
		return self.select(cursor, """SELECT * FROM sqlite_master
			WHERE type='table'
			ORDER BY name;
			""")

	def get_DB_version(self):
		return self.select("""SELECT version FROM DB_VERSION;""")


	def get_table_details(self, table_name):
		return self.select("""SELECT * FROM sqlite_master
			WHERE type='table'
			AND name=?;
			""", table_name)

	def get_tables_items(self, *tables, **kwargs):
		result = []
		for table in tables:
			try:
				result += table.load(self.cursor, **kwargs)
			except:
				_LOGGER.exception("Getting items in table %s", table)
		return result

	def delete(self, item):
		item.delete(self.cursor)
		self.connection.commit()

	def update(self, item, **kwargs):
		item.update(self.cursor, **kwargs)
		self.connection.commit()

	def contains(self, table, **kwargs):
		items = self.get_tables_items(table)
		for item in items:
			match = True
			for attr, value in kwargs.items():
				if getattr(item, attr, None) != value:
					match = False
			if match:
				return True
		return False

	def to_nmj_path(self, filepath):
		return filepath.replace(self.library_path, "")


class DatabaseItem(object):
	@classmethod
	def load(cls, cursor, **kwargs):
		where_clause = ""
		params = []
		if kwargs:
			where_clauses = []
			for key, value in kwargs.iteritems():
				where_clauses.append("%s=?" % key.upper())
				params.append(value)
			where_clause = " WHERE " + ", ".join(where_clauses)
		db_instances = cursor.execute("""SELECT %s from %s%s;""" % (
			",".join(cls.FIELDS.keys()),
			cls.TABLE,
			where_clause
		), params)
		py_instances = []
		for db_instance in db_instances:
			py_instance = cls()
			for rank, name in enumerate(cls.FIELDS.keys()):
				setattr(py_instance, name.lower(), db_instance[rank])
			py_instances.append(py_instance)
		return py_instances

	@classmethod
	def get_rows_number(cls, cursor):
		return len(cursor.execute("""SELECT * from %s;""" % cls.TABLE).fetchall())

	@classmethod
	def get_id_field(cls):
		return cls.FIELDS.items()[0][0]
	@classmethod
	def get_next_id(cls, cursor):
		current_id = cursor.execute("""SELECT MAX(%s) from %s;""" % (cls.get_id_field(), cls.TABLE)).fetchone()[0] or 0
		return current_id + 1

	@classmethod
	def purge(cls, cursor):
		cursor.execute("DELETE FROM %s;" % cls.TABLE).fetchone()

	@classmethod
	def insert(cls, cursor, **kwargs):
		_LOGGER.debug("insert %s in %s", kwargs, cls.__name__)
		params = OrderedDict()
		for key in cls.FIELDS.keys():
			if key in cls.DEFAULTS:
				params[key] = cls.DEFAULTS[key]
			elif cls.FIELDS[key] == "INTEGER":
				params[key] = 0
			else:
				params[key] = ""
		for key, value in kwargs.items():
			params[key.upper()] = value
		try:
			item_id = cls.get_next_id(cursor)
			param_values = params.values()[1:]
			cmd = """INSERT INTO %s VALUES (%d,%s)""" % (cls.TABLE, item_id, ",".join(["?"] * len(param_values)))
			_LOGGER.debug("%s.insert(%s) cmd is:\n%s\n with params : %s", cls, kwargs, cmd, param_values)
			cursor.execute(cmd, param_values)
			return item_id
		except :
			_LOGGER.exception("Cannot insert %s in %s", kwargs, cls.__name__)
			raise

	@classmethod
	def count(cls, cursor, **kwargs):
		params = []
		for key in kwargs.keys():
			params.append("%s=?" % key)
		cmd = """SELECT * from %s WHERE %s;""" % (cls.TABLE, " AND ".join(params))
		_LOGGER.debug("%s.find(%s) cmd is:\n%s", cls, kwargs, cmd)
		return len(cursor.execute(cmd, kwargs.values()).fetchall())

	@classmethod
	def create(cls, cursor):
		_LOGGER.debug("create table %s", cls.__name__)
		items = "%s %s PRIMARY KEY" % (cls.FIELDS.keys()[0], cls.FIELDS.items()[0][1])
		for column, column_type in cls.FIELDS.items()[1:]:
			items += ", %s %s" % (column, column_type)
			if column in cls.DEFAULTS:
				items += " DEFAULT '%s'" % cls.DEFAULTS[column]
		cmd = """CREATE TABLE %s (%s)""" % (cls.TABLE, items)
		_LOGGER.debug("%s.create() cmd is:\n%s", cls, cmd)
		cursor.execute(cmd)

	def get_id_value(self):
		return int(getattr(self, self.get_id_field().lower()))

	def update(self, cursor, **kwargs):
		_LOGGER.debug("update %s in %s", kwargs, self)
		query = []
		params = []
		for key, value in kwargs.iteritems():
			setattr(self, key, value)
			query.append("%s=?" % key.upper())
			params.append(value)
		try:
			cmd = """UPDATE %s SET %s WHERE %s=%s""" % (
				self.TABLE,
				", ".join(query),
				self.get_id_field(),
				self.get_id_value()
			)
			_LOGGER.debug("%s.update(%s) cmd is:\n%s\n with params : %s", self.__class__.__name__, kwargs, cmd, params)
			cursor.execute(cmd, params)
		except :
			_LOGGER.exception("Cannot update %s in %s", kwargs, self)
			raise

	def delete(self, cursor):
		cursor.execute("DELETE FROM %s WHERE %s=%s" % (
			self.TABLE,
			self.get_id_field(),
			self.get_id_value())
		)

	def __str__(self):
		attrs = ["%s=%s" % (attr, value) for attr, value in self.__dict__.items() if not attr.startswith("__") and value]
		return "%s(%s)" % (self.__class__.__name__, ",".join(attrs))
